package com.Dao.Imp;
import com.Dao.PersonalMessDao;
import com.Utils.C3P0Utils;
import com.pojo.PersonalIndex;
import com.pojo.PersonalRemark;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.springframework.stereotype.Service;
import java.sql.SQLException;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.List;
@Service
@Slf4j
public class PersonalMessDaoImp implements PersonalMessDao {
    @Override
    public List<PersonalIndex> loadMyMessage(String username) throws SQLException {
        QueryRunner queryRunner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="SELECT id,userAvatar,nickname,registTime FROM userlist where username=?";
        List<PersonalIndex> personalIndices=queryRunner.query(sql,new BeanListHandler<>(PersonalIndex.class),username);
        for(PersonalIndex person:personalIndices){
        long time=Long.parseLong(person.getRegistTime());
        Instant instant = Instant.ofEpochMilli(time);
        LocalDateTime timestampDateTime = LocalDateTime.ofInstant(instant, ZoneId.systemDefault());
        String registTime= timestampDateTime.format(DateTimeFormatter.ofPattern("yyyy年MM月dd日"));
        person.setRegistTime(registTime);
        }
        return personalIndices;
    }
    @Override
    public List<PersonalRemark> getMyRemark(String username) throws SQLException {
        QueryRunner queryRunner=new QueryRunner(C3P0Utils.getDataSource());
        String sql="SELECT r.comment, r.praise, r.reply, bd.bookName, bd.id, COALESCE(re.recommendQuantity, 0) AS recommendQuantity FROM remark r JOIN books_detail bd ON r.bookId = bd.id JOIN userlist ul ON r.userAlias = ul.username LEFT JOIN recommend re ON r.bookId = re.bookId AND ul.username = re.username WHERE ul.username = ? AND (re.username IS NULL OR re.recommendQuantity <> 0) LIMIT 6;";
        List<PersonalRemark> personalRemarkList=queryRunner.query(sql,new BeanListHandler<>(PersonalRemark.class),username);
        return  personalRemarkList;
    }
}
